﻿<%@ WebHandler Language="C#" Class="ProblemReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 问题上报查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.4.15
 **/

public class ProblemReport : IHttpHandler
{
    public bool IsReusable { get { return false; } }
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    UserImage u = new UserImage();
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream))
        {
            requestjson = reader.ReadToEnd();
        }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEmployeInfo":
                        r = getEmployeeInfo(jObject);//查询员工信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询员工信息
    Result getEmployeeInfo(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT e.employeename,e.employeeid,e.fullname,
       f.factoryname,f.factoryid,
       wc.workcentername,wc.workcenterid,
       s.shiftname,nvl(e.shiftid,te.shiftid) shiftid,
       te.teamname,te.teamid,
       eroles.rolenames
FROM employee e 
LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid 
LEFT JOIN factory f ON f.factoryid = sv.factoryid 
LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid 
LEFT JOIN operation o ON o.operationid = sv.operationid
LEFT JOIN 
( 
  SELECT e.employeeid,listagg(r.rolename,'、') WITHIN GROUP (ORDER BY r.rolename) rolenames
  FROM employeerole er 
  LEFT JOIN employee e ON e.employeeid = er.employeeid  
  LEFT JOIN roledef r ON r.roleid = er.roleid  
  GROUP BY e.employeeid 
) eroles ON eroles.employeeid = e.employeeid 
LEFT JOIN 
(
  SELECT  t.teamname,t.teamid,te.employeeid,t.shiftid 
  FROM teamemployee te 
  LEFT JOIN team t ON t.teamid = te.teamid 
) te  ON te.employeeid = e.employeeid AND ROWNUM = 1
LEFT JOIN shift s ON s.shiftid = nvl(e.shiftid,te.shiftid)
WHERE e.employeeid = '{0}'
";
        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT f.factoryname,w.workcentername,up.description,up.ufccontent,e.fullname createPerson,e.employeename createPersonName,mp.liuwei createPersonLiuWei,
       e2.fullname responsiblePerson,e2.employeename responsePersonName,mp2.liuwei responsePersonLiuWei,
       up.ufccreatedate,up.ufcchulidate,
       CASE WHEN up.ufcstatus=0 THEN '已关闭' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' 
            WHEN up.ufcstatus=3 THEN '处理完' END AS status,
       DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
       CASE WHEN (up.ufcstatus = 0 OR up.ufcstatus = 3) AND NVL(up.ufcguanbidate,up.ufcguanbidate) IS NOT NULL THEN ROUND((NVL(up.ufcguanbidate,up.ufcguanbidate)-up.ufcchulidate),2)
            WHEN (up.ufcstatus = 0 OR up.ufcstatus = 3) AND NVL(up.ufcguanbidate,up.ufcguanbidate) IS NULL THEN 0
            WHEN (up.ufcstatus = 1 OR up.ufcstatus = 2) THEN ROUND((SYSDATE-up.ufcchulidate),2) END delayday,
       up.ufcresult,w2.workcentername createWorkcenter,up.notes,up.ufcguanbidate,up.ufcwanchengdate
FROM ufcproblem up
LEFT JOIN employee e ON e.employeeid = up.ufccreateemployeeid
LEFT JOIN employee e2 ON e2.employeeid = up.ufctoemployeeid
LEFT JOIN workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid
LEFT JOIN workcenter w2 ON w2.workcenterid = sv.workcenterid
LEFT JOIN container c ON c.containerid = up.containerid
LEFT JOIN mes_userimage_mapping mp ON mp.employeename = e.employeename
LEFT JOIN mes_userimage_mapping mp2 ON mp2.employeename = e2.employeename
LEFT JOIN factory f on f.factoryid = up.factoryid
WHERE 1=1
";
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND up.factoryid='{0}'", jObject["factoryid"].ToString());
        }

        // 提出工区
        if (jObject["createWorkcenterid"] != null && jObject["createWorkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND sv.workcenterid = '{0}'", jObject["createWorkcenterid"].ToString());
        }

        // 提出人
        if (jObject["creatPerson"] != null && jObject["creatPerson"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname Like '%{0}%' ", jObject["creatPerson"].ToString());
        }

        // 提出日期
        if (jObject["createDate"] != null && jObject["createDate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createDate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.ufccreatedate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.ufccreatedate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        // 状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "已关闭")
            {
                sql += string.Format(" AND up.ufcstatus = 0");
            }
            if (jObject["status"].ToString() == "未处理")
            {
                sql += string.Format(" AND up.ufcstatus = 1");
            }
            if (jObject["status"].ToString() == "处理中")
            {
                sql += string.Format(" AND up.ufcstatus = 2");
            }
            if (jObject["status"].ToString() == "处理完")
            {
                sql += string.Format(" AND up.ufcstatus = 3");
            }
        }

        // 责任工区
        if (jObject["resWorkcenterid"] != null && jObject["resWorkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["resWorkcenterid"].ToString());
        }

        // 责任人
        if (jObject["responsePerson"] != null && jObject["responsePerson"].ToString() != "")
        {
            sql += string.Format(" AND e2.fullname LIKE '%{0}%'", jObject["responsePerson"].ToString());
        }

        // 批次号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND c.containername Like '%{0}%' ", jObject["containername"].ToString());
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);

        DataTable dt = dto.DBTable;

        dt.Columns.Add("createUserImages", typeof(string));//提出人头像
        dt.Columns.Add("responseUserImages", typeof(string));//责任人头像

        //获取提出人头像
        u.getUserImage(dt, "createUserImages", "createPersonName", "createPersonLiuWei");
        //获取责任人头像
        u.getUserImage(dt, "responseUserImages", "responsePersonName", "responsePersonLiuWei");

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,w.workcentername,up.description,up.ufccontent,e.fullname createPerson,e2.fullname responsiblePerson,
       up.ufccreatedate,up.ufcchulidate,
       CASE WHEN up.ufcstatus=0 THEN '已关闭' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' 
            WHEN up.ufcstatus=3 THEN '处理完' END AS status,
       DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
       CASE WHEN (up.ufcstatus = 0 OR up.ufcstatus = 3) AND NVL(up.ufcguanbidate,up.ufcguanbidate) IS NOT NULL THEN ROUND((NVL(up.ufcguanbidate,up.ufcguanbidate)-up.ufcchulidate),2)
            WHEN (up.ufcstatus = 0 OR up.ufcstatus = 3) AND NVL(up.ufcguanbidate,up.ufcguanbidate) IS NULL THEN 0
            WHEN (up.ufcstatus = 1 OR up.ufcstatus = 2) THEN ROUND((SYSDATE-up.ufcchulidate),2) END delayday,
       up.ufcresult,w2.workcentername createWorkcenter,up.notes,up.ufcguanbidate,up.ufcwanchengdate
FROM ufcproblem up
LEFT JOIN employee e ON e.employeeid = up.ufccreateemployeeid
LEFT JOIN employee e2 ON e2.employeeid = up.ufctoemployeeid
LEFT JOIN workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid
LEFT JOIN workcenter w2 ON w2.workcenterid = sv.workcenterid
LEFT JOIN container c ON c.containerid = up.containerid
LEFT JOIN factory f on f.factoryid = up.factoryid
WHERE 1=1
";
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND up.factoryid='{0}'", jObject["factoryid"].ToString());
        }

        // 提出工区
        if (jObject["createWorkcenterid"] != null && jObject["createWorkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND sv.workcenterid = '{0}'", jObject["createWorkcenterid"].ToString());
        }

        // 提出人
        if (jObject["creatPerson"] != null && jObject["creatPerson"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname Like '%{0}%' ", jObject["creatPerson"].ToString());
        }

        // 提出日期
        if (jObject["createDate"] != null && jObject["createDate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createDate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.ufccreatedate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.ufccreatedate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        // 状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "已关闭")
            {
                sql += string.Format(" AND up.ufcstatus = 0");
            }
            if (jObject["status"].ToString() == "未处理")
            {
                sql += string.Format(" AND up.ufcstatus = 1");
            }
            if (jObject["status"].ToString() == "处理中")
            {
                sql += string.Format(" AND up.ufcstatus = 2");
            }
            if (jObject["status"].ToString() == "处理完")
            {
                sql += string.Format(" AND up.ufcstatus = 3");
            }
        }

        // 责任工区
        if (jObject["resWorkcenterid"] != null && jObject["resWorkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["resWorkcenterid"].ToString());
        }

        // 责任人
        if (jObject["responsePerson"] != null && jObject["responsePerson"].ToString() != "")
        {
            sql += string.Format(" AND e2.fullname LIKE '%{0}%'", jObject["responsePerson"].ToString());
        }

        // 批次号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND c.containername Like '%{0}%' ", jObject["containername"].ToString());
        }

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataRow dr in dt.Rows)
        {
            if (Convert.ToDouble(dr["delayday"].ToString()) < 0)
            {
                dr["delayday"] = 0;
            }
        }


        dt.Columns["DESCRIPTION"].Caption = "问题简述";
        dt.Columns["NOTES"].Caption = "问题详述";
        dt.Columns["UFCRESULT"].Caption = "处理意见";
        dt.Columns["UFCCONTENT"].Caption = "问题类型";
        dt.Columns["RESPONSIBLEPERSON"].Caption = "责任人";
        dt.Columns["WORKCENTERNAME"].Caption = "责任单位";
        dt.Columns["CREATEPERSON"].Caption = "提出人";
        dt.Columns["CREATEWORKCENTER"].Caption = "提出单位";
        dt.Columns["UFCCREATEDATE"].Caption = "问题提出时间";
        dt.Columns["UFCCHULIDATE"].Caption = "要求完成时间";
        dt.Columns["UFCWANCHENGDATE"].Caption = "问题完成时间";
        dt.Columns["UFCGUANBIDATE"].Caption = "问题关闭时间";
        dt.Columns["STATUS"].Caption = "状态";
        dt.Columns["PRIORITY"].Caption = "需求程度";
        dt.Columns["DELAYDAY"].Caption = "超期天数";
        ExportHelper.ExportExcel(dt, "问题上报报表", true);
    }
}